﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;


namespace ClassDAL
{
    public class t_class_tearcher_course_DAL
    {

        /// <summary>
        /// 插入老师和课程的关系
        /// <param name="dicts">关系字典</param>
        /// <param name="fileid">对应哪一个excel表格</param>
        /// </summary>
        public static int InsertRelationships(Dictionary<string,List<string>> dicts,int fileid)
        { 
            // 取出数据备用
            // 课程数据
            List<t_class_course> courses = t_class_course_DAL.GetCourseListWithFileID(fileid);
            // 老师数据
            List<t_class_tearcher> teachers = t_class_teacher_DAL.GetTeacherListWithFileId(fileid);

            /*
             
             * 1.找到课程id
             * 2.找到老师id
             * 3. 进行相关性插入
             */
            StringBuilder insertBuilder = new StringBuilder();
            foreach (string course in dicts.Keys)
            {
                // 获取课程id
                int courseid = courses.Where(c => c.coursename == course).FirstOrDefault().id;
                // 开始遍历老师并获取老师id
                var teacherList = dicts[course];
                foreach (string teacherName in teacherList)
                {
                    // 获取老师id
                    int teacherId = teachers.Where(t => t.teacher == teacherName).FirstOrDefault().id;
                    // 一条SQL语句就出来了。
                    insertBuilder.AppendLine(string.Format(
                        "insert into t_class_tearcher_course values({0},{1},{2});",
                         fileid,
                         teacherId,
                         courseid
                        ));
                }

            }

            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertBuilder.ToString());

        }
    }
}
